package DAL;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import entities.Candidate;
import entities.ConnectionManager;

public class CandidateDA {
	public Candidate[] selectCandidates(Candidate user)
	{
		boolean whereClause = false;
		String query = "SELECT * from Candidate  " ;
		if(user.getCandidateID() != -1)
		{
			query += "WHERE Candidateid = " + user.getCandidateID();
			whereClause = true;
		}
				
		if(!user.getName().equals("")) 
		{
			if(whereClause)
				query += " AND [name] = '" + user.getName() + "'";
			else
				query += " WHERE [name] = '" + user.getName() + "'";
			whereClause = true;
		}
		if(!user.getEmail().equals("")) 
		{
			if(whereClause)
				query += " AND [email] = '" + user.getEmail() + "'";
			else
				query += " WHERE [email] = '" + user.getEmail() + "'";
			whereClause = true;
		}

		ConnectionManager con = new ConnectionManager();
		ResultSet rs = con.executeSelectQuery(query);
		
		ArrayList<Candidate> users = new ArrayList<Candidate>();
		
		try {
			while(rs.next())
			{
				Candidate tempUser= new Candidate();
				tempUser.setCandidateID(rs.getInt("CandidateID"));
				tempUser.setName(rs.getString("Name"));
				tempUser.setEmail(rs.getString("Email"));
				tempUser.setInfo(rs.getString("Info"));
				tempUser.setProgram(rs.getString("Program"));
				tempUser.setImage(rs.getString("Image"));
				
				users.add(tempUser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		Candidate [] temp = new Candidate [0];
		return users.toArray(temp);

	}


	public boolean updateCandidate(Candidate user)
	{
		boolean setClause = false;
		String query = "UPDATE Candidate SET " ;
		if(!user.getName().equals("")) 
		{
			query += " [Name] = '" + user.getName() + "'  ";
			setClause = true;
		}
		if(!user.getInfo().equals("")) 
		{
			if(setClause)
				query += " , info = '" + user.getInfo() + "'";
			else
				query += " info = '" + user.getInfo() + "'";
			setClause = true;
		}
		if(!user.getProgram().equals("")) 
		{
			if(setClause)
				query += " , program = '" + user.getProgram() + "'";
			else
				query += " program = '" + user.getProgram() + "'";
			setClause = true;
		}
		if(!user.getImage().equals("")) 
		{
			if(setClause)
				query += " , image = '" + user.getImage() + "'";
			else
				query += " image = '" + user.getImage() + "'";
			setClause = true;
		}
		query += " WHERE CandidateID = "+ user.getCandidateID();
		ConnectionManager con = new ConnectionManager();
		
		int x = con.executeUpdateQuery(query);
		
		return (x==1);
	}
	
	public boolean insertCandidate(Candidate user)
	{
		String query = "INSERT INTO Candidate ([name] ,email , info , image , program ) VALUES " +
				"('" + user.getName() + "','" +user.getEmail() + "' , '" + user.getInfo() + "'" +  " , '" + user.getImage() + "'" 
						+ " , '" + user.getProgram() + "')";
		
		ConnectionManager con = new ConnectionManager();
		
		int x = con.executeUpdateQuery(query);
		
		return x==1;
	}
	public int insertCandidateForVoting(Candidate user)
	{
		try
		{
		String query = "INSERT INTO Candidate ([name] ,email , info , image , program ) VALUES " +
				"('" + user.getName() + "','" +user.getEmail() + "' , '" + user.getInfo() + "'" +  " , '" + user.getImage() + "'" 
						+ " , '" + user.getProgram() + "')";
		
		ConnectionManager con = new ConnectionManager();
		
		int x = con.executeUpdateQuery(query);
		
		String iden="select scope_identity() as 'id'";
		ResultSet rs = con.executeSelectQuery(iden);
		rs.next();
		int id=rs.getInt("id");
		return id;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return -1;
	}
	
	public int deleteCandidate(Candidate user)
	{
		boolean whereClause = false;
		String query = "DELETE FROM Candidate ";
		if(user.getCandidateID() != -1)
		{
			query += "WHERE Candidateid = " + user.getCandidateID();
			whereClause = true;
		}
				
		if(!user.getName().equals("")) 
		{
			if(whereClause)
				query += " AND [name] = '" + user.getName() + "'";
			else
				query += " WHERE [name] = '" + user.getName() + "'";
			whereClause = true;
		}
		if(!user.getEmail().equals("")) 
		{
			if(whereClause)
				query += " AND [email] = '" + user.getEmail() + "'";
			else
				query += " WHERE [email] = '" + user.getEmail() + "'";
			whereClause = true;
		}
		
		ConnectionManager con = new ConnectionManager();
		
		int x = con.executeUpdateQuery(query);
		
		return x;
	}
	
	
	public static void main(String[] args) {
		Candidate u = new Candidate();

		
		//u.setEmail("asdsadasd");
		u.setImage("a");
		u.setInfo("a");
		u.setName("a");
		u.setProgram("as");
		u.setEmail("email");
		u.setCandidateID(3);
		CandidateDA da = new CandidateDA();
		System.out.println(da.deleteCandidate(u));
	}

}
